package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Responsible for read the database using a SQL command.
 * 
 * @author Cassio Soares Cabral
 * 
 */
public class Reader {

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DATABASE_URL = "jdbc:mysql://localhost/marketingjoy";

	static String USER = utils.Config.getDBUserName();
	static String PASSWORD = utils.Config.getDBPassword();

	/**
	 * Execute a query from database and return a ResultSet object.
	 * 
	 * @param SQLCommand
	 * @return ResultSet
	 * @throws Exception
	 */
	public ResultSet readerFromDB(String SQLCommand) throws Exception {
		Connection connection = null; // manages connection
		Statement statement = null; // query statement
		ResultSet resultSet = null;
		// connect to database books and query database
		try {
			Class.forName(JDBC_DRIVER); // load database driver class

			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);

			// create Statement for querying database
			statement = connection.createStatement();
			// create the resultSet with the SQL command, passed as parameter
			resultSet = statement.executeQuery(SQLCommand);

			if (resultSet == null) {
				throw new Exception("ResultSet equals null");
			}

		} // end try
		catch (SQLException sqlException) {
			sqlException.printStackTrace();
		} // end catch
		catch (ClassNotFoundException classNotFound) {
			classNotFound.printStackTrace();
		} // end catch
		finally // ensure statement and connection are closed properly
		{
			try {
				statement.close();
				connection.close();
			} // end try
			catch (Exception exception) {
				exception.printStackTrace();
			} // end catch
		} // end finally
		return resultSet;

	}

	/**
	 * Get the name of all sponsors.
	 * 
	 * @return sponsorsNameTemp - A list with the sponsor's name
	 * @throws SQLException
	 *             - If an error occur during the query
	 * @throws ClassNotFoundException
	 *             - If the JDBC Driver cannot find the Class
	 */
	public List<String> getSponsorsName() throws SQLException,

	ClassNotFoundException {
		List<String> sponsorsNameTemp = new ArrayList<String>();
		Connection connection;
		Statement statement;
		ResultSet resultSet;
		// load database driver class
		Class.forName(JDBC_DRIVER);
		// establish connection to database
		connection = DriverManager.getConnection(DATABASE_URL, USER, PASSWORD);
		// create Statement for querying database
		statement = connection.createStatement();
		// create the resultSet with the SQL command, passed as parameter
		resultSet = statement.executeQuery("SELECT name FROM sponsor");
		// Pick up the results in database and put in a list
		while (resultSet.next()) {
			sponsorsNameTemp.add(resultSet.getString("name"));
		}
		return sponsorsNameTemp;
	}

	/**
	 * Gets the monthly of all sponsors.
	 * 
	 * @return sponsorsMonthlyTemp - A list with the sponsors's monthly
	 * @throws SQLException
	 *             - If an error occur during the query
	 * @throws ClassNotFoundException
	 *             - If the JDBC Driver cannot find the Class
	 */
	public List<Double> getSponsorMonthly() throws SQLException,
			ClassNotFoundException {
		List<Double> sponsorsMonthlyTemp = new ArrayList<Double>();
		Connection connection;
		Statement statement;
		ResultSet resultSet;
		// load database driver class
		Class.forName(JDBC_DRIVER);
		// establish connection to database
		connection = DriverManager.getConnection(DATABASE_URL, USER, PASSWORD);
		// create Statement for querying database
		statement = connection.createStatement();
		// create the resultSet with the SQL command, passed as parameter
		resultSet = statement.executeQuery("SELECT monthly FROM sponsor");
		// Pick up the results in database and put in a list
		while (resultSet.next()) {
			sponsorsMonthlyTemp.add(resultSet.getDouble("monthly"));
		}
		return sponsorsMonthlyTemp;
	}

	/**
	 * Gets the type of all sponsors
	 * 
	 * @return sponsorsTypeTemp - A list with sponsors's type
	 * @throws SQLException
	 *             - If an error occur during the query
	 * @throws ClassNotFoundException
	 *             - If the JDBC Driver cannot find the Class
	 */
	public List<String> getSponsorType() throws SQLException,
			ClassNotFoundException {
		List<String> sponsorsTypeTemp = new ArrayList<String>();
		Connection connection;
		Statement statement;
		ResultSet resultSet;
		// load database driver class
		Class.forName(JDBC_DRIVER);
		// establish connection to database
		connection = DriverManager.getConnection(DATABASE_URL, USER, PASSWORD);
		// create Statement for querying database
		statement = connection.createStatement();
		// create the resultSet with the SQL command, passed as parameter
		resultSet = statement.executeQuery("SELECT type FROM sponsor");
		// Pick up the results in database and put in a list
		while (resultSet.next()) {
			sponsorsTypeTemp.add(resultSet.getString("type"));
		}
		return sponsorsTypeTemp;
	}

	/**
	 * Get from database the path(s) owned by the sponsor passed as parameter.
	 * 
	 * @param sponsorName
	 * @return List with strings(paths)
	 */
	public List<String> getPathOwnedBySponsor(String sponsorName) {

		List<String> pathsTemp = new ArrayList<String>();
		Connection connection;
		Statement statement;
		ResultSet resultSet;
		try {
			// load database driver class
			Class.forName(JDBC_DRIVER);
			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);
			// create Statement for querying database
			statement = connection.createStatement();
			// create the resultSet with the SQL command, passed as parameter
			resultSet = statement
					.executeQuery("SELECT path FROM video WHERE name = '"
							+ sponsorName + "'");
			// Pick up the results in database and put in a list
			while (resultSet.next()) {
				pathsTemp.add(resultSet.getString("path"));
			}
		} catch (Exception e) {

		}

		return pathsTemp;

	}

	/**
	 * Get a list with the path related with sponsors who have the type passed
	 * as parameter.
	 * 
	 * @param type
	 * @return List with the paths
	 */
	private List<String> getPathWithSponsorTypeEquals(String type) {

		List<String> pathsTemp = new ArrayList<String>();
		Connection connection;
		Statement statement;
		ResultSet resultSet;
		try {
			// load database driver class
			Class.forName(JDBC_DRIVER);
			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);
			// create Statement for querying database
			statement = connection.createStatement();
			// create the resultSet with the SQL command, passed as parameter
			resultSet = statement
					.executeQuery("SELECT path FROM video INNER JOIN sponsor ON video.name = sponsor.name WHERE type = '"
							+ type + "'"); // ON sponsor.type ='" + type + "'");
			// Pick up the results in database and put in a list
			while (resultSet.next()) {
				pathsTemp.add(resultSet.getString("path"));
			}
		} catch (Exception e) {

		}
		return pathsTemp;

	}

	/**
	 * Get a list with the path related with sponsors who have the type equals
	 * gold.
	 * 
	 * @return List with the paths
	 */
	public List<String> getPathWithSponsorTypeGold() {
		return getPathWithSponsorTypeEquals("gold");
	}

	/**
	 * Get a list with the path related with sponsors who have the type equals
	 * silver.
	 * 
	 * @return List with the paths
	 */
	public List<String> getPathWithSponsorTypeSilver() {
		return getPathWithSponsorTypeEquals("silver");
	}

	/**
	 * Get a list with the path related with sponsors who have the type equals
	 * platinum .
	 * 
	 * @return List with the paths
	 */
	public List<String> getPathWithSponsorTypePlatinum() {
		return getPathWithSponsorTypeEquals("platinum");
	}

	/**
	 * Get a list with all feeds in the database.
	 * 
	 * @return feeds in the database.
	 */
	public List<String> getFeedsAvailable() {
		List<String> feedsAvailableTemp = new ArrayList<String>();
		Connection connection;
		Statement statement;
		ResultSet resultSet;
		try {
			// load database driver class
			Class.forName(JDBC_DRIVER);
			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);
			// create Statement for querying database
			statement = connection.createStatement();
			// create the resultSet with the SQL command, passed as parameter
			resultSet = statement.executeQuery("SELECT feed FROM rss");
			// Pick up the results in database and put in a list
			while (resultSet.next()) {
				feedsAvailableTemp.add(resultSet.getString("feed"));
			}
		} catch (Exception e) {

		}
		return feedsAvailableTemp;

	}

	/**
	 * Get the feed URL.
	 * 
	 * @param feed
	 * @return URL from feed
	 */
	public String getFeedUrl(String feed) {
		return null;

	}

	/**
	 * Get from database the news(titles) associated with the feed passed as
	 * parameter.
	 * 
	 * @param feed
	 * @return List with strings(news titles).
	 */
	public List<String> getNewsFromFeed(String feed) {
		List<String> news = new ArrayList<String>();
		Connection connection;
		Statement statement;
		ResultSet resultSet;
		try {
			// load database driver class
			Class.forName(JDBC_DRIVER);
			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);
			// create Statement for querying database
			statement = connection.createStatement();
			// create the resultSet with the SQL command, passed as parameter
			resultSet = statement.executeQuery("SELECT titles FROM news");
			// Pick up the results in database and put in a list
			while (resultSet.next()) {
				news.add(resultSet.getString("titles"));
			}
		} catch (Exception e) {

		}
		return news;

	}

	/**
	 * Get the videos(path) of entertainment, which
	 * 
	 * @return Entertainment Videos(path) - The path of the entertainment video
	 *         file
	 */
	public List<String> getEntertainmentVideos() {
		return getPathOwnedBySponsor("EntertainmentVideos");

	}

	/**
	 * Get the date of the last update realized in the news.
	 * 
	 * @return last date of update.
	 */
	public String getLastRssUpdate() {
		return null;

	}

	/**
	 * Show with prints the query passed as parameter.
	 * 
	 * @param SQLCommand
	 *            - Enter here the SQLCommand
	 */
	public void showInConsole(String SQLCommand) {
		Connection connection = null; // manages connection
		Statement statement = null; // query statement

		// connect to database books and query database
		try {
			Class.forName(JDBC_DRIVER); // load database driver class

			// establish connection to database
			connection = DriverManager.getConnection(DATABASE_URL, USER,
					PASSWORD);

			// create Statement for querying database
			statement = connection.createStatement();

			// query database

			ResultSet resultSet = statement.executeQuery(SQLCommand);

			java.sql.ResultSetMetaData metaData = resultSet.getMetaData();
			int numberOfColumns = metaData.getColumnCount();
			Object objAtual = null;
			for (int i = 1; i <= numberOfColumns; i++)
				System.out.printf("%-8s\t", metaData.getColumnName(i));
			System.out.println();

			while (resultSet.next()) {

				for (int i = 1; i <= numberOfColumns; i++)
					objAtual = resultSet.getObject(i);
				System.out.printf("%-8s\t", objAtual);
				System.out.println();
			} // end while
		} // end try
		catch (SQLException sqlException) {
			sqlException.printStackTrace();

		} // end catch
		catch (ClassNotFoundException classNotFound) {
			classNotFound.printStackTrace();

		} // end catch
		finally // ensure statement and connection are closed properly
		{
			try {
				statement.close();
				connection.close();
			} // end try
			catch (Exception exception) {
				exception.printStackTrace();
			} // end catch
		} // end finally
	}
}